package cn.pconline.autoclub.task;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author chensy
 */
public class ClubMemberPetrolTask {

    private static Logger log = LoggerFactory.getLogger(ClubMemberPetrolTask.class);

    public static void setLog(Logger log) {
        ClubMemberPetrolTask.log = log;
    }
    private String allClubIdSql = "SELECT club_id,vip_num FROM ac_club";
    private String listmemberRank = "SELECT id,petrol FROM ac_member_info where club_id = ?";
    //共统计了2196个车友会，耗时698.407秒
    private String dayPetrolSql = "SELECT sum(petrol) as sum_petrol, user_id, club_id FROM ac_member_petrol_log WHERE create_at >= ? AND create_at <= ? group by user_id, club_id";
    private String updateStatPetrolSql = "UPDATE ac_member_stat s SET s.day_petrol = ?,s.day_petrol_update_at = ?,s.update_at= ? WHERE s.club_id = ? AND user_id = ?";
    private String insertStatPetrolSql = "INSERT INTO ac_member_stat (club_id, user_id, day_petrol, day_petrol_update_at, week_petrol, week_petrol_update_at,update_at) VALUES (?, ?, ?, ?,?, ?, ?)";
    private String updateMemberRankSql = " update ac_member_info set rank = ? where id = ? ";
    private String updateVipNum = " UPDATE ac_club_stat SET total_vip = ? where club_id = ? ";

    public static void main(String[] args) throws SQLException {
        ClubMemberPetrolTask task = new ClubMemberPetrolTask();
        task.dailyClubMemberPetrolTask(new Date());
        }

    /**
     * 统计车友会每天（昨天）增加的油值
     *
     * @param cur
     * @throws SQLException
     */
    public void dailyClubMemberPetrolTask(Date cur) throws SQLException {
        Connection conn = DBUtils.getConnection();
        conn.setAutoCommit(false);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            log.info("---->每天统计车友会会员的油值---->准备执行……");
            long start = System.currentTimeMillis();

            //从日志中获取会员昨天增加的油值
            ps = conn.prepareStatement(DBUtils.getSql(dayPetrolSql, "ac_member_petrol_log", DateUtils.getLastDay(cur)));
            ps.setTimestamp(1, DateUtils.getLastDayStart(cur));
            ps.setTimestamp(2, DateUtils.getLastDayEnd(cur));
            int count = 0;
            rs = ps.executeQuery();
            while (rs.next()) {
                long sumPetrol = rs.getLong("sum_petrol");
                long clubId = rs.getLong("club_id");
                long userId = rs.getLong("user_id");
                Timestamp updateDate = DateUtils.getTimeStamp(cur);

                ps = conn.prepareStatement(updateStatPetrolSql);
                ps.setLong(1, sumPetrol);
                ps.setTimestamp(2, updateDate);
                ps.setTimestamp(3, updateDate);
                ps.setLong(4, clubId);
                ps.setLong(5, userId);

                int uc = ps.executeUpdate();
                if (uc == 0) {//没统计记录，则创建
                    ps = conn.prepareStatement(insertStatPetrolSql);
                    ps.setLong(1, clubId);
                    ps.setLong(2, userId);
                    ps.setLong(3, sumPetrol);
                    ps.setTimestamp(4, updateDate);
                    ps.setLong(5, 0);
                    ps.setTimestamp(6, updateDate);
                    ps.setTimestamp(7, updateDate);
                    ps.execute();
                }
                log.info("---->每天统计车友会会员的油值---->第{}个会员[{},{}]统计完毕", ++count, clubId, userId);
            }

            long end = System.currentTimeMillis();
            log.info("---->每天统计车友会会员的油值---->执行完毕！共{}个会员，耗时{}秒", count, (end - start) / 1000.0);
        } catch (Exception e) {
            log.error("dailyClubMemberPetrolTask exception!", e);
        } finally {
            conn.commit();
            conn.setAutoCommit(true);
            DBUtils.releaseConn(conn, ps, rs);
        }
    }

    /**
     * 统计每个车友会会员排名
     */
    public void memberRankTask() throws SQLException {

        Connection conn = DBUtils.getConnection();
        conn.setAutoCommit(false);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //查询所有车友会ID
            List<Club> listClubId = new ArrayList();
            ps = conn.prepareStatement(allClubIdSql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Club club = new Club();
                club.setClubId(rs.getLong("club_id"));
                club.setVipNum(rs.getInt("vip_num"));
                listClubId.add(club);
            }
            //逐个车友会统计昨天的油值
            for (Club club : listClubId) {
                long clubId = club.getClubId();
                List<Rank> listMember = new ArrayList<Rank>(1024);
                //查询车友会下的所有会员的ID
                ps = conn.prepareStatement(listmemberRank);
                ps.setLong(1, clubId);
                rs = ps.executeQuery();
                while (rs.next()) {
                    Rank r = new Rank();
                    r.setId(rs.getLong("id"));
                    r.setPetrol(rs.getInt("petrol"));
                    listMember.add(r);
                }
                //车友会个人积分排名
                Collections.sort(listMember, new Comparator<Rank>() {
                    public int compare(Rank o1, Rank o2) {
                        return (int) (o2.getPetrol() - o1.getPetrol());
                    }
                });

                //更新全国排名排名
                for (int i = 0; i < listMember.size(); i++) {
                    ps = conn.prepareStatement(updateMemberRankSql);
                    ps.setLong(1, i + 1);
                    ps.setLong(2, listMember.get(i).getId());
                    ps.executeUpdate();
                }

                //同步会员人数到ac_club_stat表
                ps = conn.prepareStatement(updateVipNum);
                ps.setLong(1, club.getVipNum());
                ps.setLong(2, clubId);
                ps.executeUpdate();
            }

            log.info(String.format("%s clubs member have been stat!", listClubId.size()));
        } catch (Exception e) {
            log.error("dailyClubMemberPetrolTask exception!", e);
        } finally {
            conn.commit();
            conn.setAutoCommit(true);
            DBUtils.releaseConn(conn, ps, rs);
        }
    }
    //TODO 任务执行完后，有去访问clear_member_info 这个接口批量清缓存（更新rank字段）可以考虑直接在这个任务里面做清除操作
}
